/**
* Project: yui3-common-web
 * Class ImportExcelUtils
 * Version 1.0
 * File Created at 2018年8月7日
 * $Id$
 * 
 * Copyright 2010-2015 Yui.com Corporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Yui Personal. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Yui.com.
 */
package yui.comn.hub.extension.poi;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import yui.comn.hub.data.parser.HubDataToDtoParser;
import yui.comn.hub.model.HubXmlColumn;
 

/**
 * excel导入
 * @author yuyi (1060771195@qq.com)
 * HubXmlColumn column1 = new HubXmlColumn("登陆名", "sysUserVo.usNm");
   HubXmlColumn column2 = new HubXmlColumn("昵称", "sysUserVo.nkNm");
 */
public class ImportExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ImportExcelUtils.class);
    
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    
    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");// 格式化 number为整
    private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐
    
//  private static final DecimalFormat df_per_ = new DecimalFormat("0.00%");//格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%
//  private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 格式化日期字符串
    
    private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss");
    private static final DecimalFormat DECIMAL_FORMAT_NUMBER  = new DecimalFormat("0.00E000"); //格式化科学计数器
    private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配
    
    
    /**
     * 对外提供读取excel的方法
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz, List<HubXmlColumn> columns, int titleRowNum, int bodyRowNum)  {
    	String extension = StringUtils.substringAfterLast(file.getOriginalFilename(), ".");
        
        if (StringUtils.equalsIgnoreCase(XLS, extension)
                || StringUtils.equalsIgnoreCase(XLSX, extension)) {
            try {
                // 过滤有枚举类自动产生得数据
                List<HubXmlColumn> cols = new ArrayList<>();
                for (HubXmlColumn col : columns) {
                    if (StringUtils.isNotBlank(col.getMapper())) {
                        cols.add(col);
                    }
                }
                return readExcel(file.getInputStream(), clazz, cols, titleRowNum, bodyRowNum);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
        
        return null;
    }
    
    /**
     * 对外提供读取excel的方法
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz, List<HubXmlColumn> columns)  {
    	return readExcel(file, clazz, columns, 0, 1);
    }
    
    /**
     * 获取excel数据将之转换成对象列表
     */
    private static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz, 
    		List<HubXmlColumn> columns, int titleRowNum, int bodyRowNum) {
        List<T> dataList = new ArrayList<T>();
        Workbook workbook = null;
        String mapper = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            
            Sheet sheet = workbook.getSheetAt(0);
            
            List<String> titles = new ArrayList<>();
            Row titleRow = sheet.getRow(titleRowNum);
            for (int k = titleRow.getFirstCellNum(); k < titleRow.getLastCellNum(); k++) {
                titles.add(titleRow.getCell(k).getStringCellValue());
            }
            //检查excel头文件
            vaildExcelTitle(columns, titles);
            
            for (int i = sheet.getFirstRowNum() + bodyRowNum; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                
                //实例化最外层对象
                @SuppressWarnings("unchecked")
                T obj = (T) HubDataToDtoParser.getInstance().toObjByClass(clazz);
                
                for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                    //对象赋值
                    HubXmlColumn column = columns.get(j);
                    // mapper = column.getMapper();
                    Object cellValue = getCellValue(row.getCell(j));
                    if (null != cellValue) {
                        HubDataToDtoParser.getInstance().toObjSetVal(obj, column, cellValue);
                    }
                }
                
                dataList.add(obj);
            }
            
            return dataList;
        } catch (Exception e) {
            String errMsg = "解析excel文件数据，并转化对象列表失败, mapper=" + mapper + ", " + e.getMessage();
            logger.error(errMsg, e);
            throw new RuntimeException(errMsg, e);
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
    }
    
    /**
     * 检查excel头文件
     */
    public static void vaildExcelTitle(List<HubXmlColumn> columns, List<String> titles) {
//        if (columns.size() != titles.size()) {
//            String errMsg = "excel列数和xml列数不对";
//            logger.warn(errMsg);
//            throw new SystemException("excel列数和xml列数不对");
//        }
        
        for (int i = 0; i < titles.size(); i++) {
            // 过滤通过枚举自动产生得字段
            String title = columns.get(i).getDescr();
            if (!StringUtils.equals(title, titles.get(i))) {
                String errMsg = "excel标题不对，检查excel是不是有错误";
                logger.warn(errMsg);
                throw new RuntimeException("excel标题不对，检查excel是不是有错误");
            }
        }
        
    }
    
    /**
     * 获取excel单元格数据
     */
    private static Object getCellValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        Object value = null;
        switch (cell.getCellTypeEnum()) {
        case _NONE:
            break;
        case STRING:
            value = cell.getStringCellValue();
            break;
        case NUMERIC:
            if(DateUtil.isCellDateFormatted(cell)){ //日期
                value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//统一转成 yyyy/MM/dd
            } else if("General".equals(cell.getCellStyle().getDataFormatString())){
            	String val = String.valueOf(cell.getNumericCellValue());
            	if (!StringUtils.endsWith(val, ".0")) {
            		value = cell.getNumericCellValue();
            	} else {
            		value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
            	}
            } else if("@".equals(cell.getCellStyle().getDataFormatString())
                    || "0_ ".equals(cell.getCellStyle().getDataFormatString())){
                //文本  or 常规 or 整型数值
                value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
            } else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型  
                value = cell.getNumericCellValue();  //直接显示
            } else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
                value = cell.getNumericCellValue(); //待完善           
                value = DECIMAL_FORMAT_NUMBER.format(value);
            } else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比                      
                value = cell.getNumericCellValue(); //待完善
                value = DECIMAL_FORMAT_PERCENT.format(value);
            } else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
                value = cell.getNumericCellValue(); ////待完善
            } else { 
            	// 货币       
                // value = cell.getNumericCellValue();
                // value = DecimalFormat.getCurrencyInstance().format(value);
            	value = cell.getNumericCellValue();
            }
            break;
        case BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case BLANK:
            //value = ",";
            break;
        default:
            value = cell.toString();
        }
        return value;
    }

}
